<?php

namespace app\admin\controller;

use think\Controller;
use think\Db;

class Test extends Controller
{
    public function index()
    {
//        2.1. 快捷查询
        $row = Db::table('user')
            ->where('id', 5)
            ->find();
        $row = Db::table('user')
//            ->where('id','<=', 5)
            ->where('id', 'in', [1, 3, 5])
            ->select();
        $row = Db::table('user')
            ->where('age', '>', 18)
            ->where('name', 'like', '%王%')
            ->select();

        $row = Db::table('user')
            ->where(['sex' => 0])
            ->whereOr(['province' => '上海'])
            ->select();

//        2.2. 批量查询
        $row = Db::name('user')
            ->where([
                'id' => ['between', '10,20'],
                'sex' => ['eq', 0]
            ])->select();

        $row = Db::name('user')
            ->where('sex', 1)
            ->where('id', ['between', '1,3'], ['in', [6, 66, 666]], 'or')
            ->select();

//        2.3. 区间查询
        $row = Db::name('user')
            ->where('id', ['>', 2], ['<', 15])
            ->select();

//        2.4. 多表查询
//            1). 手动多表
        $row = Db::field(['u.name'=>'un','l.name'=>'ln'])
                ->table(['hc_user'=> 'u', 'hc_lover'=>'l'])
                ->where('u.id = l.user_id')
                ->select();

//            2). JOIN
        $row = Db::table('hc_user')
                ->alias('u')
                ->field(['u.name'=>'uname','l.name'=>'lname'])
                ->join('hc_lover l', 'u.id = l.user_id')
                ->order('uname', 'DESC')
                ->select();

//            3). 视图
        $row = Db::view('hc_user', ['name'=>'uname'])
                ->view('hc_lover', ['name'=>'lname'],'hc_user.id = hc_lover.user_id')
                ->select();


//        2.5. 聚合查询
        $row = Db::table('hc_user')->count();
        $row = Db::table('hc_user')->avg('age');

        $row = Db::table('hc_user')
            ->field('province, count(id)')
            ->group('province')
            ->select();

        $row = Db::table('hc_user')
            ->field('province, group_concat(name)')
            ->group('province')
            ->select();


//        2.6. 子查询
//            1.fetchSql 方式
//            2.buildSql 方式
        // 构建子查询SQL
        $query = Db::table('hc_user')
            ->field('id, name, sex, age')
            ->where('sex',0)
            ->buildSql();
//        dump($query);
        // 组装 完整的SQL
        $row = Db::table($query . ' u')
            ->where('u.age','>',18)
            ->order('id', 'desc')
            ->select();

        dump($row);
    }
}
